using System.Data;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.RawMaterialSuppliers;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.RawMaterialSuppliers
{
    /// <summary>
    /// 导出供应商列表到Excel
    /// </summary>
    public class ExportRawMaterialSuppliersCommandHandler : IRequestHandler<ExportRawMaterialSuppliersCommand, byte[]>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<ExportRawMaterialSuppliersCommandHandler> _logger;

        public ExportRawMaterialSuppliersCommandHandler(IDbConnectionFactory connectionFactory, ILogger<ExportRawMaterialSuppliersCommandHandler> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<byte[]> Handle(ExportRawMaterialSuppliersCommand request, CancellationToken cancellationToken)
        {
            using var conn = _connectionFactory.CreateConnectionWithRetry();

            var sql = @"SELECT 
                            s.Id,
                            s.Supplier_Name,
                            s.Supplier_Alias,
                            s.Short_Name,
                            s.Province_City,
                            s.Supply_Category,
                            s.Supplier_Level,
                            s.Supply_Products,
                            s.Company_Address,
                            s.Postal_Code,
                            s.Phone,
                            s.Remarks,
                            s.Status,
                            sb.Contact_Name   AS BusinessContact,
                            sb.Mobile_Phone   AS BusinessContact_Phone,
                            sf.Contact_Name   AS FinanceContact,
                            sf.Mobile_Phone   AS FinanceContact_Phone,
                            si.Bank_Name,
                            si.Bank_Address,
                            si.Account_Number,
                            si.Tax_Number
                        FROM Supplier s
                        LEFT JOIN SupplierBusiness sb ON s.Id = sb.Supplier_Id AND sb.IsDel = 0
                        LEFT JOIN SupplierFinance sf  ON s.Id = sf.Supplier_Id AND sf.IsDel = 0
                        LEFT JOIN SupplierInvoice si  ON s.Id = si.Supplier_Id AND si.IsDel = 0
                        WHERE s.IsDeleted = 0";

            var param = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.Supplier_Name))
            {
                sql += " AND s.Supplier_Name LIKE @Supplier_Name";
                param.Add("@Supplier_Name", $"%{request.Supplier_Name.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.BusinessContact_Name))
            {
                sql += " AND (sb.Contact_Name LIKE @BusinessContact_Name)";
                param.Add("@BusinessContact_Name", $"%{request.BusinessContact_Name.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.Supply_Products))
            {
                sql += " AND s.Supply_Products = @Supply_Products";
                param.Add("@Supply_Products", request.Supply_Products.Trim());
            }

            sql += " ORDER BY s.Id DESC";

            var rows = await conn.QueryAsync(sql, param);

            // 构建 Excel（.xls 格式）
            IWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("Suppliers");

            int r = 0;
            // Header
            var header = sheet.CreateRow(r++);
            string[] headers = new[]
            {
                "供应商名称", "供应商别名", "简称", "省市区", "供应类别",
                "供应商等级", "供应料品", "公司地址", "邮编", "电话", "备注", "状态",
                "业务联系人", "业务联系人手机号", "财务联系人", "财务联系人手机号",
                "开票银行名称", "开票银行地址", "开票银行账号", "开票税号"
            };
            for (int i = 0; i < headers.Length; i++) header.CreateCell(i).SetCellValue(headers[i]);

            foreach (var row in rows)
            {
                var dataRow = sheet.CreateRow(r++);
                int c = 0;
                dataRow.CreateCell(c++).SetCellValue((string?)row.Supplier_Name ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Supplier_Alias ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Short_Name ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Province_City ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Supply_Category ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Supplier_Level ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Supply_Products ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Company_Address ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Postal_Code ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Phone ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Remarks ?? "");
                dataRow.CreateCell(c++).SetCellValue((row.Status is int s) ? s : 0);
                dataRow.CreateCell(c++).SetCellValue((string?)row.BusinessContact ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.BusinessContact_Phone ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.FinanceContact ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.FinanceContact_Phone ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Bank_Name ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Bank_Address ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Account_Number ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Tax_Number ?? "");
            }

            for (int i = 0; i < headers.Length; i++) sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
    }
}
